﻿/**********************************弘石项目*******************************************
* 功能描述:考勤统计，考勤汇总，项目概括查询，高级查询，公告管理，资质管理，人力资源管理,电表，消防门
* 作者:刘子奇
* 时间:2021-12-22
* 
**********************************弘石项目*******************************************/
using Dapper;
using HongStone.Domain.AttendanceManagement;
using HongStone.Domain.ContractModel;
using HongStone.Domain.OperationsManagement;
using HongStone.Domain.SystemSetupModel.InstitutionSettingModel;
using HongStone.Ince;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HongStone.Repository
{
   public class attendanceDal: IattInce
    {
        private readonly HongStoneContext db;
        public attendanceDal(HongStoneContext hongStoneContext)
        {
            db = hongStoneContext;
        }
        /// <summary>
        /// 员工信息考勤统计
        /// </summary>
        /// <returns></returns>
        public List<kqtj> GetStaffs()
        {
            var q = from a in db.staffs
                    join b in db.departments on a.DeptId equals b.DeptId
                    select new kqtj
                    { 
                        DeptId=a.DeptId,
                        StaffName=a.StaffName,
                        DeptName=b.DeptName,
                        DeptIds=a.DeptIds
                    };
            return q.ToList();
        }
        /// <summary>
        /// 年假统计
        /// </summary>
        /// <returns></returns>
        public List<njtj> GetNjtjs()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<njtj>("select datediff(dd,BeginLeaveTime,FinishLeaveTime) as tian,ApplyForId,ApplyName from Clocking_inApplyFo").ToList();
                return list;
            }
        }
        /// <summary>
        /// 出差统计
        /// </summary>
        /// <returns></returns>
        public List<chuchai> GetChuchais()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<chuchai>("select ApplyForId,ApplyName,GongLi,BeginLeaveTime,(GongLi*20) as feiyong,EntryName from Clocking_inApplyFo where ApplyForTpe=2 and GongLi>35").ToList();
                return list;
            }
        }
        /// <summary>
        /// 差旅补贴
        /// </summary>
        /// <returns></returns>
        public List<chuchai> getchailv()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<chuchai>("select ApplyForId,ApplyName,GongLi,BeginLeaveTime,datediff(dd,BeginLeaveTime,FinishLeaveTime) as tianshu,(datediff(dd,BeginLeaveTime,FinishLeaveTime)*300) as feiyong,EntryName from Clocking_inApplyFo where ApplyForTpe=2").ToList();
                return list;
            }
        }
        /// <summary>
        /// 带薪年假
        /// </summary>
        /// <returns></returns>
        public List<daixin> GetDaixins()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<daixin>("select ApplyForId,ApplyName,datediff(hh,BeginLeaveTime,FinishLeaveTime) as xiaoshi from Clocking_inApplyFo").ToList();
                return list;
            }
        }
        public List<Yuehuizong> GetYuehuizongs()
        {
            var query = from a in db.staffs
                        join b in db.departments on a.DeptId equals b.DeptId
                        select  new Yuehuizong
                        {
                            DeptId=a.DeptId,
                            DeptName=b.DeptName,
                            StaffName=a.StaffName,
                            StaffId=a.StaffId,
                            jiaban=a.jiaban
                        };
            return query.ToList();
        }
        /// <summary>
        /// 计算数
        /// </summary>
        /// <returns></returns>
        public List<suanshu> GetSuanshus()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = "declare @c int=(select count(*) from Staff)" +
                    "";
                sql += "select count(*) as renshu,avg(Staff.jiaban) pingjun,(select avg(1.0 * jiaban)from(select jiaban from Staff order by jiaban offset(@c-1)/2 rows fetch next 1+(@c-1/2) rows only) as x) as zhongwei,max(jiaban) zuida,min(jiaban)as zuixiao,(max(jiaban)-min(jiaban)) as jicha  from Staff join department on Staff.DeptId=Department.DeptId";
                var list = conn.Query<suanshu>(sql).ToList();
                return list;
            }
        }
        /// <summary>
        /// 年分析
        /// </summary>
        /// <returns></returns>
        public List<suanshu> nianfenxi()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = "declare @c int=(select count(*) from Staff)" +
                    "";
                sql += "select count(*) as renshu,avg(Staff.jiaban) pingjun,(select avg(1.0 * jiaban)from(select jiaban from Staff order by jiaban offset(@c-1)/2 rows fetch next 1+(@c-1/2) rows only) as x) as zhongwei,max(jiaban) zuida,min(jiaban)as zuixiao,(max(jiaban)-min(jiaban)) as jicha  from Staff join department on Staff.DeptId=Department.DeptId group by DeptName";
                var list = conn.Query<suanshu>(sql).ToList();
                return list;
            }
        }
        /// <summary>
        /// 年汇总
        /// </summary>
        /// <returns></returns>
        public List<nhuizong> GetNhuizongs()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<nhuizong>("select DeptName,Staff.StaffId,StaffName,sum(jiaban) as heji from Staff join department on Staff.DeptId=Department.DeptId group by DeptName,Staff.StaffId,StaffName").ToList();
                return list;
            }
        }
        /// <summary>
        /// 月图表
        /// </summary>
        /// <returns></returns>
        public List<suanshu> yuetubiao()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<suanshu>("select avg(jiaban) as zhpj,max(jiaban) as jljb,sum(jiaban/4) as zpj from  Staff join department on Staff.DeptId=Department.DeptId ").ToList();
                return list;
            }
        }
        /// <summary>
        /// 项目概括
        /// </summary>
        /// <returns></returns>
        public List<Project> Getgaikuo(out int totalcount,int pageindex,int pagesize,string ProjectName)
        {
            var query = db.projects.ToList();
            if(!string.IsNullOrEmpty(ProjectName))
            {
                query = query.Where(p => p.ProjectName.Contains(ProjectName)).ToList();
            }
            totalcount = query.Count();

            var list = query.Skip((pageindex-1)*pagesize).Take(pagesize).Select(p => new Project
            {
                ProjectId = p.ProjectId,
                ProjectName=p.ProjectName,
                BeginTime=p.BeginTime,
                CompletionTime=p.CompletionTime,
                DesignId=p.DesignId
            }).ToList();
            return list;
        }
        /// <summary>
        /// 日志信息添加
        /// </summary>
        /// <param name="rizhis"></param>
        /// <returns></returns>
        public int Addrizhi(rizhis rizhis)
        {
            db.rizhis.Add(rizhis);
            return db.SaveChanges();
        }
        /// <summary>
        /// 日志信息显示
        /// </summary>
        /// <param name="Title"></param>
        /// <returns></returns>
        public List<rizhis> GetRizhis(string Title)
        {
            var list = db.rizhis.ToList();
            if(!string.IsNullOrEmpty(Title))
            {
                list = list.Where(p => p.Title.Contains(Title)).ToList();
            }
            return list;
        }
        /// <summary>
        /// 日志信息反填
        /// </summary>
        /// <param name="Rid"></param>
        /// <returns></returns>
        public rizhis GetRizhisBYid(int Rid)
        {
            return db.rizhis.Find(Rid);
        }
        /// <summary>
        /// 修改日志信息
        /// </summary>
        /// <param name="rizhis"></param>
        /// <returns></returns>
        public int updaterizhi(rizhis rizhis)
        {
            db.Entry(rizhis).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            return db.SaveChanges();
        }
        /// <summary>
        /// 批量删除日志信息
        /// </summary>
        /// <param name="Rid"></param>
        /// <returns></returns>
        public int DelAllrizhi(string Ids)
        {
            var arr = db.rizhis.Where(p => Ids.Contains(Convert.ToString(p.Rid)));
            db.rizhis.RemoveRange(arr);
            return db.SaveChanges();
        }
        /// <summary>
        /// 公告显示
        /// </summary>
        /// <param name="Gtitle"></param>
        /// <returns></returns>
        public List<gonggaos> GetGonggaos(string Gtitle)
        {
            var list = db.gonggaos.ToList();
            if(!string.IsNullOrEmpty(Gtitle))
            {
                list = list.Where(p => p.Gtitle.Contains(Gtitle)).ToList();
            }
            return list;
        }
        /// <summary>
        /// 公告添加
        /// </summary>
        /// <param name="gonggaos"></param>
        /// <returns></returns>
        public int Addgonggao(gonggaos gonggaos)
        {
            db.gonggaos.Add(gonggaos);
            return db.SaveChanges();
        }
        /// <summary>
        /// 批量删除公告
        /// </summary>
        /// <param name="Ids"></param>
        /// <returns></returns>
       public int Delallgonggao(string Ids)
        {
            var arr = db.gonggaos.Where(p => Ids.Contains(Convert.ToString(p.Gid)));
            db.gonggaos.RemoveRange(arr);
            return db.SaveChanges();
        }
        /// <summary>
        /// 高级查询
        /// </summary>
        /// <returns></returns>

        public List<gaoji> GetGaojis()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
               return conn.Query<gaoji>("select ProjectName,ProjectId,Note from Project").ToList();
            }
        }
        /// <summary>
        /// 高级查询合同
        /// </summary>
        /// <returns></returns>

        public List<gaoji> GetGaojiht()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                return conn.Query<gaoji>("select ContractName,Contractremarks from Thecontract").ToList();
            }
        }
        /// <summary>
        /// 资质信息添加
        /// </summary>
        /// <param name="zizhi"></param>
        /// <returns></returns>
        public int Addzizhi(zizhi zizhi)
        {
            db.zizhi.Add(zizhi);
            return db.SaveChanges();
        }
        /// <summary>
        /// 资质信息查询显示
        /// </summary>
        /// <param name="Zname"></param>
        /// <param name="jibie"></param>
        /// <param name="YearTime"></param>
        /// <returns></returns>
        public List<zizhi> GetZizhis(string Zname,string jibie,DateTime? YearTime)
        {
            var list = db.zizhi.ToList();
            if(!string.IsNullOrEmpty(Zname))
            {
                list = list.Where(p => p.Zname.Contains(Zname)).ToList();
            }
            if(!string.IsNullOrEmpty(jibie))
            {
                list = list.Where(p => p.jibie == jibie).ToList();
            }
            if(YearTime!=null)
            {
                list = list.Where(p => p.YearTime == YearTime).ToList();
            }
            return list;
        }
       /// <summary>
       /// 资质批量删除
       /// </summary>
       /// <param name="Ids"></param>
       /// <returns></returns>
        public int Delallzizhi(string Ids)
        {
            var arr = db.zizhi.Where(p => Ids.Contains(Convert.ToString(p.Zid)));
            db.zizhi.RemoveRange(arr);
            return db.SaveChanges();
        }
        /// <summary>
        /// 查看资质信息
        /// </summary>
        /// <param name="Zid"></param>
        /// <returns></returns>
        public zizhi GetZizhibyid(int Zid)
        {
            return db.zizhi.Find(Zid);
        }
        /// <summary>
        /// 资质信息修改
        /// </summary>
        /// <param name="zizhi"></param>
        /// <returns></returns>
        public int updatezizhi(zizhi zizhi)
        {
            db.Entry(zizhi).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            return db.SaveChanges();
        }
        /// <summary>
        /// 人力资源管理
        /// </summary>
        /// <returns></returns>
        public List<rlzy> GetRlzies()
        {
            var query = from a in db.waijian
                        join b in db.logins on a.LoginId equals b.LoginId
                        join c in db.employees on a.EmployeeId equals c.EmployeeId
                        join d in db.educations on a.EducationId equals d.EducationId
                        join e in db.employeeCertifivates on a.CertifivateId equals e.CertifivateId
                        join f in db.employeesWorks on a.WorkId equals f.WorkId
                        join g in db.employeeManpowers on a.ManpowerId equals g.ManpowerId
                        join z in db.dictionariesTables on c.DeptId equals z.DictionariesId
                        join z1 in db.dictionariesTables on c.ManagementId equals z1.DictionariesId
                        join z2 in db.dictionariesTables on c.TechnologyId equals z2.DictionariesId
                        join z3 in db.dictionariesTables on e.ProfessionalId equals z3.DictionariesId
                        select new rlzy
                        {
                            wid=a.wid,
                            LoginId=b.LoginId,
                            EmployeeId=c.EmployeeId,
                            EducationId=d.EducationId,
                            CertifivateId=e.CertifivateId,
                            WorkId=f.WorkId,
                            ManpowerId=g.ManpowerId,
                            LoginAccount=b.LoginAccount,
                            LoginName=b.LoginName,
                            StateName=b.State==1?"在职":"离职",
                            DeptName=z.DictionariesName,
                            Sexs=c.Sex==1?"男":"女",
                            MajorName=z.DictionariesName,
                            ManagementName=z1.DictionariesName,
                            TechnologyName=z2.DictionariesName,
                            IdNumber=c.IdNumber,
                            Address=c.Address,
                            DateBirth=c.DateBirth,
                            Age=c.Age,
                            Phone=c.Phone,
                            ExtensionNumber=c.ExtensionNumber,
                            ProfessionalName=z3.DictionariesName,
                            EvaluateTime=e.EvaluateTime,
                            Register=e.Register,
                            RegisterTime=e.RegisterTime,
                            RegisterAddr=e.RegisterAddr,
                            School=d.School,
                            Degree=d.Degree,
                            Major=d.Major,
                            Time=d.Time,
                            WorkTime=f.WorkTime,
                            EntryTime=f.EntryTime,
                            WorkBetweenTime=f.WorkBetweenTime,
                            EntryBetweenTime=f.EntryBetweenTime,
                            WorkYear=f.WorkYear,
                            TestCycle=f.TestCycle,
                            PostAge=f.PostAge,
                            LocationArchives=g.LocationArchives,
                            OffBase=g.OffBase,
                            YearTime=g.YearTime,
                            General=g.General,
                            PositiveTime=g.PositiveTime,
                            Salary=g.Salary,
                            TrafficSubsidy=g.TrafficSubsidy,
                            NetPay=g.NetPay,
                            SocialSecurity=g.SocialSecurity,
                            ReservedFund=g.ReservedFund,
                            Remark=g.Remark,
                            PretestBonus=g.PretestBonus,
                            AnnualPay=g.AnnualPay,
                            DeptId=c.DeptId,
                            Sex=c.Sex,
                            MajorId=c.MajorId,
                            ManagementId=c.ManagementId,
                            TechnologyId=c.TechnologyId,
                            ProfessionalId = e.ProfessionalId,
                            PhoneAllowance=g.PhoneAllowance,
                            State=b.State
                        };
            return query.ToList();
        }
        /// <summary>
        /// 查看员工登录信息
        /// </summary>
        /// <param name="LoginId"></param>
        /// <returns></returns>
        public List<Login> GetLogins(int? LoginId)
        {
            var list = db.logins.ToList();
            if (LoginId != null)
            {
                list = list.Where(p => p.LoginId == LoginId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 员工基本信息
        /// </summary>
        /// <param name="EmployeeId"></param>
        /// <returns></returns>
        public List<Employee> GetEmployees(int? EmployeeId)
        {
            var list = db.employees.ToList();
            if (EmployeeId != null)
            {
                list = list.Where(p => p.EmployeeId == EmployeeId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 员工教育信息
        /// </summary>
        /// <param name="EducationId"></param>
        /// <returns></returns>
        public List<Education> GetEducations(int? EducationId)
        {
            var list = db.educations.ToList();
            if (EducationId != null)
            {
                list = list.Where(p => p.EducationId == EducationId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 员工证书情况
        /// </summary>
        /// <param name="CertifivateId"></param>
        /// <returns></returns>
        public List<EmployeeCertifivate> GetEmployeeCertifivates(int? CertifivateId)
        {
            var list = db.employeeCertifivates.ToList();
            if (CertifivateId != null)
            {
                list = list.Where(p => p.CertifivateId == CertifivateId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 员工工作情况
        /// </summary>
        /// <param name="WorkId"></param>
        /// <returns></returns>
        public List<EmployeesWork> GetEmployeesWorks(int? WorkId)
        {
            var list = db.employeesWorks.ToList();
            if(WorkId!=null)
            {
                list = list.Where(p => p.WorkId == WorkId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 员工人力信息
        /// </summary>
        /// <param name="ManpowerId"></param>
        /// <returns></returns>
        public List<EmployeeManpower> GetEmployeeManpowers(int? ManpowerId)
        {
            var list = db.employeeManpowers.ToList();
            if (ManpowerId != null)
            {
                list = list.Where(p => p.ManpowerId == ManpowerId).ToList();
            }
            return list;
        }
        /// <summary>
        /// 电表
        /// </summary>
        /// <param name="dianRequest"></param>
        /// <returns></returns>
        public List<Meteringequipment> ShowMeteringequipment(DianRequest dianRequest)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = "SELECT * FROM Meteringequipment WHERE 1=1 ";
                if (dianRequest.Building != "")
                {
                    sql += $" AND MBuilding = '{dianRequest.Building}' ";
                }
                if (dianRequest.Unit != "")
                {
                    sql += $" AND MUnit = '{dianRequest.Unit}'";
                }
                if (dianRequest.Floor != "")
                {
                    sql += $" AND MFloor = '{dianRequest.Floor}'";
                }
                if (dianRequest.State != "")
                {
                    if (dianRequest.State == "1")
                    {
                        sql += $" AND MState = 1 ";
                    }
                    else
                    {
                        sql += $" AND MState = 0 ";
                    }
                }
                if (dianRequest.Room != "")
                {
                    sql += $" AND MRoom LIKE '%'+'{dianRequest.Room}'+'%'";
                }
               var result = conn.Query<Meteringequipment>(sql).ToList();
                return result;
            }
           
        }
        /// <summary>
        /// 消防门设备列表显示
        /// </summary>
        /// <param name="dianRequest">查询参数</param>
        /// <returns></returns>
        public List<Fireexitdoor> ShowFireexitdoor(DianRequest dianRequest)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = "SELECT * FROM Fireexitdoor WHERE 1=1 ";
                if (dianRequest.Building != "")
                {
                    sql += $" AND FBuilding = '{dianRequest.Building}' ";
                }
                if (dianRequest.Unit != "")
                {
                    sql += $" AND FUnit = '{dianRequest.Unit}'";
                }
                if (dianRequest.Floor != "")
                {
                    sql += $" AND FFloor = '{dianRequest.Floor}'";
                }
                if (dianRequest.State != "")
                {
                    if (dianRequest.State == "1")
                    {
                        sql += $" AND FState = 1 ";
                    }
                    else
                    {
                        sql += $" AND FState = 0 ";
                    }
                }
                if (dianRequest.Room != "")
                {
                    sql += $" AND FRoom LIKE '%'+'{dianRequest.Room}'+'%'";
                }
                var list = conn.Query<Fireexitdoor>(sql).ToList();
                return list;
            }
          
        }
        /// <summary>
        /// 消防门修改状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateF1State(int id)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {

                var sql = $"SELECT * FROM Fireexitdoor WHERE FId = '{id}'";
                var item = conn.Query<Fireexitdoor>(sql).FirstOrDefault();
                if (item.FState == true)
                {
                    return conn.Execute($"UPDATE Fireexitdoor SET FState = 0 WHERE FId = '{id}'");
                }
                else
                {
                    return conn.Execute($"UPDATE Fireexitdoor SET FState = 1 WHERE FId = '{id}'");
                }
            }
        }

    }
}
